﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.ComponentModel;
using System.Data;
using System.Transactions;
using System.Data.SqlClient;

using Global.Class.Library;
using DataTransferObject;
using DataTransferObject.MetaDataList;
using DataAccessLayer;
using DataAccessLayer.MetaDataList;
using DataAccessLayer.MetaDataList.ListCustomerNameDTSTableAdapters;


namespace BusinessLogicLayer.MetaDataList
{
    public class ListCustomerNameBLL : NotifyPropertyChangeObject
    {
        public GlobalEnum.TaskID TaskID { get { return GlobalEnum.TaskID.ListCustomerName; } }

        private UserInformation userOrganization;

        private ListCustomerNameMaster listCustomerNameMaster;

        private BindingList<ListCustomerNameDetail> listCustomerNameDetailList;

        private BindingList<ListCustomerNameSalesmen> listCustomerNameSalesmenList;

        private int viewCustomer;


        public ListCustomerNameBLL()
        {
            try
            {
                if (GlobalVariables.shouldRestoreProcedure) RestoreProcedure();


                userOrganization = new UserInformation();

                listCustomerNameMaster = new ListCustomerNameMaster();

                this.listCustomerNameDetailList = new BindingList<ListCustomerNameDetail>();

                this.listCustomerNameSalesmenList = new BindingList<ListCustomerNameSalesmen>();

                GlobalDefaultValue.Apply(this);


                this.ListCustomerNameMaster.PropertyChanged += new PropertyChangedEventHandler(ListCustomerNameMaster_PropertyChanged);

                this.ListCustomerNameDetailList.ListChanged += new ListChangedEventHandler(ListCustomerNameDetailList_ListChanged);

                this.ListCustomerNameSalesmenList.ListChanged += new ListChangedEventHandler(ListCustomerNameSalesmenList_ListChanged);

            }
            catch (Exception exception)
            {
                throw exception;
            }
        }

        private void ListCustomerNameMaster_PropertyChanged(object sender, PropertyChangedEventArgs e)
        {
            this.SetDirty();
        }

        private void ListCustomerNameDetailList_ListChanged(object sender, ListChangedEventArgs e)
        {
            this.SetDirty();
        }

        private void ListCustomerNameSalesmenList_ListChanged(object sender, ListChangedEventArgs e)
        {
            this.SetDirty();
        }




        #region <Adapter>




        private ListCustomerNameAddressAreaListingTableAdapter customerNameAddressAreaListingTableAdapter;
        protected ListCustomerNameAddressAreaListingTableAdapter CustomerNameAddressAreaListingTableAdapter
        {
            get
            {
                if (customerNameAddressAreaListingTableAdapter == null) customerNameAddressAreaListingTableAdapter = new ListCustomerNameAddressAreaListingTableAdapter();
                return customerNameAddressAreaListingTableAdapter;
            }
        }


        private ListCustomerNameCustomerCategoryListingTableAdapter customerNameCustomerCategoryListingTableAdapter;
        protected ListCustomerNameCustomerCategoryListingTableAdapter CustomerNameCustomerCategoryListingTableAdapter
        {
            get
            {
                if (customerNameCustomerCategoryListingTableAdapter == null) customerNameCustomerCategoryListingTableAdapter = new ListCustomerNameCustomerCategoryListingTableAdapter();
                return customerNameCustomerCategoryListingTableAdapter;
            }
        }

        private ListCustomerNameCustomerChannelListingTableAdapter customerNameCustomerChannelListingTableAdapter;
        protected ListCustomerNameCustomerChannelListingTableAdapter CustomerNameCustomerChannelListingTableAdapter
        {
            get
            {
                if (customerNameCustomerChannelListingTableAdapter == null) customerNameCustomerChannelListingTableAdapter = new ListCustomerNameCustomerChannelListingTableAdapter();
                return customerNameCustomerChannelListingTableAdapter;
            }
        }

        private ListCustomerNameCustomerTypeListingTableAdapter customerNameCustomerTypeListingTableAdapter;
        protected ListCustomerNameCustomerTypeListingTableAdapter CustomerNameCustomerTypeListingTableAdapter
        {
            get
            {
                if (customerNameCustomerTypeListingTableAdapter == null) customerNameCustomerTypeListingTableAdapter = new ListCustomerNameCustomerTypeListingTableAdapter();
                return customerNameCustomerTypeListingTableAdapter;
            }
        }

        private ListCustomerNameMasterCustomerListingTableAdapter customerNameMasterCustomerListingTableAdapter;
        protected ListCustomerNameMasterCustomerListingTableAdapter CustomerNameMasterCustomerListingTableAdapter
        {
            get
            {
                if (customerNameMasterCustomerListingTableAdapter == null) customerNameMasterCustomerListingTableAdapter = new ListCustomerNameMasterCustomerListingTableAdapter();
                return customerNameMasterCustomerListingTableAdapter;
            }
        }

        private ListCustomerNameTenderCustomerListingTableAdapter customerNameTenderCustomerListingTableAdapter;
        protected ListCustomerNameTenderCustomerListingTableAdapter CustomerNameTenderCustomerListingTableAdapter
        {
            get
            {
                if (customerNameTenderCustomerListingTableAdapter == null) customerNameTenderCustomerListingTableAdapter = new ListCustomerNameTenderCustomerListingTableAdapter();
                return customerNameTenderCustomerListingTableAdapter;
            }
        }




        private ListCustomerNameTableAdapter masterTableAdapter;
        protected ListCustomerNameTableAdapter MasterTableAdapter
        {
            get
            {
                if (masterTableAdapter == null) masterTableAdapter = new ListCustomerNameTableAdapter();
                return masterTableAdapter;
            }
        }

        private ListCustomerNameDetailTableAdapter detailTableAdapter;
        protected ListCustomerNameDetailTableAdapter DetailTableAdapter
        {
            get
            {
                if (detailTableAdapter == null) detailTableAdapter = new ListCustomerNameDetailTableAdapter();
                return detailTableAdapter;
            }
        }

        private ListCustomerNameSalesmenTableAdapter salesmenDetailTableAdapter;
        protected ListCustomerNameSalesmenTableAdapter SalesmenDetailTableAdapter
        {
            get
            {
                if (salesmenDetailTableAdapter == null) salesmenDetailTableAdapter = new ListCustomerNameSalesmenTableAdapter();
                return salesmenDetailTableAdapter;
            }
        }

        private GetNewSerialIDTableAdapter getNewSerialIDTableAdapter;
        protected GetNewSerialIDTableAdapter GetNewSerialIDTableAdapter
        {
            get
            {
                if (getNewSerialIDTableAdapter == null) getNewSerialIDTableAdapter = new GetNewSerialIDTableAdapter();
                return getNewSerialIDTableAdapter;
            }
        }


        #endregion <Adapter>

        #region <Storage>

        public UserInformation UserOrganization
        {
            get { return this.userOrganization; }
            set { this.userOrganization = value; }
        }



        public ListCustomerNameDTS.ListCustomerNameEmptyListingDataTable CustomerNameEmptyListing()
        {
            return new ListCustomerNameDTS.ListCustomerNameEmptyListingDataTable();
        }

        public ListCustomerNameDTS.ListCustomerNameAddressAreaListingDataTable CustomerNameAddressAreaListing(int addressAreaID, int salesmenID)
        {
            return this.CustomerNameAddressAreaListingTableAdapter.GetData(addressAreaID, salesmenID, 0, "");
        }

        public ListCustomerNameDTS.ListCustomerNameCustomerCategoryListingDataTable CustomerNameCustomerCategoryListing(int customerCategoryID, int salesmenID)
        {
            return this.CustomerNameCustomerCategoryListingTableAdapter.GetData(customerCategoryID, salesmenID);
        }

        public ListCustomerNameDTS.ListCustomerNameCustomerChannelListingDataTable CustomerNameCustomerChannelListing(int customerChannelID, int salesmenID)
        {
            return this.CustomerNameCustomerChannelListingTableAdapter.GetData(customerChannelID, salesmenID);
        }

        public ListCustomerNameDTS.ListCustomerNameCustomerTypeListingDataTable CustomerNameCustomerTypeListing(int customerTypeID, int salesmenID)
        {
            return this.CustomerNameCustomerTypeListingTableAdapter.GetData(customerTypeID, salesmenID);
        }

        public ListCustomerNameDTS.ListCustomerNameMasterCustomerListingDataTable CustomerNameMasterCustomerListing(int salesmenID)
        {
            return this.CustomerNameMasterCustomerListingTableAdapter.GetData(salesmenID);
        }

        public ListCustomerNameDTS.ListCustomerNameTenderCustomerListingDataTable CustomerNameTenderCustomerListing(int salesmenID)
        {
            return this.CustomerNameTenderCustomerListingTableAdapter.GetData(salesmenID);
        }





        public ListCustomerNameMaster ListCustomerNameMaster
        {
            get { return this.listCustomerNameMaster; }
        }

        public BindingList<ListCustomerNameDetail> ListCustomerNameDetailList
        {
            get { return this.listCustomerNameDetailList; }
        }

        public BindingList<ListCustomerNameSalesmen> ListCustomerNameSalesmenList
        {
            get { return this.listCustomerNameSalesmenList; }
        }


        #endregion <Storage>

        #region Properties

        #region <Primary Key>

        public int CustomerID   //Primary Key
        {
            get { return this.ListCustomerNameMaster.CustomerID; }
            private set
            {
                if (this.ListCustomerNameMaster.CustomerID != value)
                {
                    this.StopTracking();

                    this.ListCustomerNameMaster.CustomerID = value;

                    this.ListCustomerNameGetMaster();
                    this.ListCustomerNameGetDetail();

                    this.StartTracking();
                    this.Reset();
                }

            }
        }

        #endregion <Primary Key>

        private void ListCustomerNameGetMaster()
        {
            if (this.CustomerID > 0)
            {
                ListCustomerNameDTS.ListCustomerNameDataTable masterDataTable = this.MasterTableAdapter.GetData(this.CustomerID);


                if (masterDataTable.Count > 0)
                {
                    this.ListCustomerNameMaster.StopTracking();

                    this.ListCustomerNameMaster.EntryDate = masterDataTable[0].EntryDate;


                    this.ListCustomerNameMaster.CustomerCategoryID = masterDataTable[0].CustomerCategoryID;
                    this.ListCustomerNameMaster.CustomerTypeID = masterDataTable[0].CustomerTypeID;
                    this.ListCustomerNameMaster.SalesmenID = masterDataTable[0].SalesmenID;
                    this.ListCustomerNameMaster.AddressAreaID = masterDataTable[0].AddressAreaID;
                    this.ListCustomerNameMaster.DistributorID = masterDataTable[0].DistributorID;
                    this.ListCustomerNameMaster.CustomerGroupID = masterDataTable[0].CustomerGroupID;
                    this.ListCustomerNameMaster.MasterCustomerID = masterDataTable[0].MasterCustomerID;
                    this.ListCustomerNameMaster.CustomerChannelID = masterDataTable[0].CustomerChannelID;

                    this.ListCustomerNameMaster.Description = masterDataTable[0].Description;
                    this.ListCustomerNameMaster.DescriptionOfficial = masterDataTable[0].DescriptionOfficial;
                    this.ListCustomerNameMaster.DescriptionOriginal = masterDataTable[0].DescriptionOriginal;
                    this.ListCustomerNameMaster.VATCode = masterDataTable[0].VATCode;
                    this.ListCustomerNameMaster.ContactName = masterDataTable[0].ContactName;
                    this.ListCustomerNameMaster.AttentionName = masterDataTable[0].AttentionName;
                    this.ListCustomerNameMaster.Telephone = masterDataTable[0].Telephone;
                    this.ListCustomerNameMaster.EMailAddress = masterDataTable[0].EMailAddress;
                    this.ListCustomerNameMaster.AddressNo = masterDataTable[0].AddressNo;
                    this.ListCustomerNameMaster.AddressStreet = masterDataTable[0].AddressStreet;
                    this.ListCustomerNameMaster.Other = masterDataTable[0].Other;

                    this.ListCustomerNameMaster.InActive = masterDataTable[0].InActive;
                    this.ListCustomerNameMaster.DependentOnly = masterDataTable[0].DependentOnly;
                    this.ListCustomerNameMaster.IsMasterCustomer = masterDataTable[0].IsMasterCustomer;
                    this.ListCustomerNameMaster.IsTenderCustomer = masterDataTable[0].IsTenderCustomer;


                    this.ListCustomerNameMaster.StartTracking();

                    this.ListCustomerNameMaster.Reset();

                    this.UserOrganization.UserID = masterDataTable[0].UserID;
                    this.UserOrganization.UserOrganizationID = masterDataTable[0].UserOrganizationID;
                }
                else throw new System.ArgumentException("Insufficient get data");


            }
            else
            {
                GlobalDefaultValue.Apply(this.ListCustomerNameMaster);
                this.ListCustomerNameMaster.EntryDate = DateTime.Today;
                this.ListCustomerNameMaster.Reset();
            }
        }


        private void ListCustomerNameGetDetail()
        {
            this.listCustomerNameDetailList.RaiseListChangedEvents = false;
            this.listCustomerNameDetailList.Clear();
            if (this.CustomerID >= 0)
            {

                ListCustomerNameDTS.ListCustomerNameDetailDataTable detailDataTable = this.DetailTableAdapter.GetData(this.CustomerID);

                if (detailDataTable.Count > 0)
                {
                    foreach (ListCustomerNameDTS.ListCustomerNameDetailRow detailRow in detailDataTable.Rows)
                    {
                        this.listCustomerNameDetailList.Add(new ListCustomerNameDetail(detailRow.CustomerModelID, detailRow.Reference, detailRow.Description, detailRow.Other, detailRow.InActive));
                    }
                }
            }
            this.listCustomerNameDetailList.RaiseListChangedEvents = true;
            this.listCustomerNameDetailList.ResetBindings();

            this.listCustomerNameSalesmenList.RaiseListChangedEvents = false;
            this.listCustomerNameSalesmenList.Clear();
            if (this.CustomerID >= 0)
            {

                ListCustomerNameDTS.ListCustomerNameSalesmenDataTable detailDataTable = this.SalesmenDetailTableAdapter.GetData(this.CustomerID);

                if (detailDataTable.Count > 0)
                {
                    foreach (ListCustomerNameDTS.ListCustomerNameSalesmenRow detailRow in detailDataTable.Rows)
                    {
                        this.listCustomerNameSalesmenList.Add(new ListCustomerNameSalesmen(detailRow.SalesmenID, detailRow.Description, detailRow.Other, detailRow.InActive));
                    }
                }
            }
            this.listCustomerNameSalesmenList.RaiseListChangedEvents = true;
            this.listCustomerNameSalesmenList.ResetBindings();
        }

        #endregion Properties





        #region Object Permission

        public override bool IsValid
        {
            get
            {
                List<ListCustomerNameDetail> inValidListCustomerNameDetail = this.ListCustomerNameDetailList.Where(listCustomerNameDetail => !listCustomerNameDetail.IsValid).ToList();
                List<ListCustomerNameSalesmen> inValidListCustomerNameSalesmen = this.ListCustomerNameSalesmenList.Where(listCustomerNameSalesmen => !listCustomerNameSalesmen.IsValid).ToList();
                return this.ListCustomerNameMaster.IsValid && inValidListCustomerNameDetail.Count == 0 && inValidListCustomerNameSalesmen.Count == 0;
            }
        }

        public bool ReadOnly
        {
            get
            {
                try
                {
                    return GlobalUserPermission.GetUserReadOnly(GlobalVariables.GlobalUserInformation.UserID, this.TaskID);
                }
                catch
                {
                    return true;
                }
            }
        }

        public bool Editable
        {
            get
            {
                try
                {
                    if (!GlobalUserPermission.GetUserEditable(GlobalVariables.GlobalUserInformation.UserID, this.TaskID, this.UserOrganization.UserOrganizationID)) return false;

                    return GlobalUserPermission.GetEditable("ListCustomerNameEditable", this.CustomerID);
                }
                catch
                {
                    return false;
                }
            }
        }

        public bool Verifiable
        {
            get
            {
                try
                {
                    if (!GlobalUserPermission.GetUserEditable(GlobalVariables.GlobalUserInformation.UserID, GlobalEnum.TaskID.ListCustomerNameVerifiable, this.UserOrganization.UserOrganizationID)) return false;

                    return GlobalUserPermission.GetEditable("ListCustomerNameEditable", this.CustomerID);
                }
                catch
                {
                    return false;
                }
            }
        }

        public bool Unverifiable
        {
            get
            {
                try
                {
                    if (!GlobalUserPermission.GetUserEditable(GlobalVariables.GlobalUserInformation.UserID, GlobalEnum.TaskID.ListCustomerNameUnverifiable, this.UserOrganization.UserOrganizationID)) return false;

                    return GlobalUserPermission.GetEditable("ListCustomerNameEditable", this.CustomerID);
                }
                catch
                {
                    return false;
                }
            }
        }


        #endregion Object Permission






        public void Fill(int customerID)
        {
            if (this.CustomerID == customerID) this.CustomerID = -1; //Enforce to reload
            this.CustomerID = customerID;
        }

        public void New()
        {
            if (this.CustomerID == 0) this.CustomerID = -1;
            this.CustomerID = 0;
        }

        public void Edit()
        {

        }

        public int GetNewSerialID(int salesmenID)
        {
            int serialmaxID;

            try
            {
                ListCustomerNameDTS.GetNewSerialIDDataTable getNewSerialIDDataTable = GetNewSerialIDTableAdapter.GetData(salesmenID);

                if (getNewSerialIDDataTable != null)
                {
                    if (getNewSerialIDDataTable.Rows.Count > 0)
                    {
                        serialmaxID = Convert.ToInt32(getNewSerialIDDataTable.Rows[0][getNewSerialIDDataTable.MaxIDColumn].ToString()) + 1;
                    }
                    else
                    {
                        serialmaxID = 1;
                    }
                }
                else
                {
                    serialmaxID = 1;
                }
            }
            catch (System.Exception exception)
            {
                throw exception;
            }

            return serialmaxID;
        }

        #region Save & Delete Method


        public bool Save()
        {
            int customerID = 0;

            try
            {

                if (!this.SaveValidate()) throw new System.ArgumentException("Insufficient save", "Save validate");

                using (TransactionScope transactionScope = new TransactionScope())
                {
                    if (this.ReadOnly) throw new System.ArgumentException("Insufficient save", "Uneditable");

                    if (!this.SaveMaster(ref customerID)) throw new System.ArgumentException("Insufficient save", "Save master");

                    if (!this.SaveDetail(customerID)) throw new System.ArgumentException("Insufficient save", "Save detail");

                    transactionScope.Complete();
                }

                this.Fill(customerID);
                return true;
            }
            catch (System.Exception exception)
            {
                throw exception;
            }
        }


        public bool Delete()
        {
            if (this.listCustomerNameMaster.CustomerID <= 0) return false;

            try
            {

                if (!this.SaveValidate()) throw new System.ArgumentException("Insufficient delete", "Delete validate");

                using (TransactionScope transactionScope = new TransactionScope())
                {
                    if (!this.Editable) throw new System.ArgumentException("Insufficient delete", "Uneditable");

                    if (!this.SaveUndo(this.listCustomerNameMaster.CustomerID)) throw new System.ArgumentException("Insufficient delete", "Delete detail");

                    if (this.MasterTableAdapter.Delete(this.listCustomerNameMaster.CustomerID) != 1) throw new System.ArgumentException("Insufficient delete", "Delete master");

                    transactionScope.Complete();
                }
                this.Fill(0);
                return true;
            }
            catch (System.Exception exception)
            {
                throw exception;
            }
        }


        private bool SaveValidate()
        {
            ExceptionTable exceptionTable = new ExceptionTable(new string[2, 2] { { "ExceptionCategory", "System.String" }, { "ExceptionDescription", "System.String" } });

            this.UserOrganization = GlobalUserPermission.GetUserInformation(this.UserOrganization.UserID, this.ListCustomerNameMaster.EntryDate);

            if (this.UserOrganization.UserID <= 0 || this.UserOrganization.UserOrganizationID <= 0) exceptionTable.AddException(new string[] { GlobalVariables.stringFieldRequired, "User information" });

            if (exceptionTable.Table.Rows.Count <= 0 && this.IsValid) return true; else throw new CustomException("Save validate", exceptionTable.Table);
        }


        private bool SaveMaster(ref int customerID)
        {
            ListCustomerNameDTS.ListCustomerNameDataTable masterDataTable;
            ListCustomerNameDTS.ListCustomerNameRow masterRow;

            if (this.listCustomerNameMaster.CustomerID <= 0) //Add
            {
                masterDataTable = new ListCustomerNameDTS.ListCustomerNameDataTable();
                masterRow = masterDataTable.NewListCustomerNameRow();

                this.listCustomerNameMaster.Description = this.GetDescriptionUPI(this.listCustomerNameMaster.AddressAreaID);
                //If Not lSaveEventLog(Me.ETaskID, Me.CustomerID, Me.Description, Me.DescriptionOfficial, Me.Other, EEventTakenID.EAddNew, "Add New") Then GoTo ERR_HANDLER
            }
            else //Edit
            {
                if (!this.SaveUndo(listCustomerNameMaster.CustomerID)) throw new System.ArgumentException("Insufficient save", "Save undo");
                masterDataTable = this.MasterTableAdapter.GetData(listCustomerNameMaster.CustomerID);
                if (masterDataTable.Count > 0) masterRow = masterDataTable[0]; else throw new System.ArgumentException("Insufficient save", "Get for edit");
                //If Not lSaveEventLog(Me.ETaskID, Me.CustomerID, Me.Description, Me.DescriptionOfficial, Me.Other, EEventTakenID.EEdit, "Modify") Then GoTo ERR_HANDLER
            }

            masterRow.EntryDate = this.listCustomerNameMaster.EntryDate;

            masterRow.CustomerCategoryID = this.listCustomerNameMaster.CustomerCategoryID;
            masterRow.CustomerTypeID = this.listCustomerNameMaster.CustomerTypeID;
            masterRow.SalesmenID = this.listCustomerNameMaster.SalesmenID;
            masterRow.AddressAreaID = this.listCustomerNameMaster.AddressAreaID;
            masterRow.DistributorID = this.listCustomerNameMaster.DistributorID;
            masterRow.CustomerGroupID = this.listCustomerNameMaster.CustomerGroupID;
            masterRow.MasterCustomerID = this.listCustomerNameMaster.MasterCustomerID;
            masterRow.CustomerChannelID = this.listCustomerNameMaster.CustomerChannelID;

            masterRow.Description = this.listCustomerNameMaster.Description;
            masterRow.DescriptionOfficial = this.listCustomerNameMaster.DescriptionOfficial;
            masterRow.DescriptionOriginal = this.listCustomerNameMaster.DescriptionOriginal;
            masterRow.VATCode = this.listCustomerNameMaster.VATCode;
            masterRow.ContactName = this.listCustomerNameMaster.ContactName;
            masterRow.AttentionName = this.listCustomerNameMaster.AttentionName;
            masterRow.Telephone = this.listCustomerNameMaster.Telephone;
            masterRow.EMailAddress = this.listCustomerNameMaster.EMailAddress;
            masterRow.AddressNo = this.listCustomerNameMaster.AddressNo;
            masterRow.AddressStreet = this.listCustomerNameMaster.AddressStreet;
            masterRow.Other = this.listCustomerNameMaster.Other;

            masterRow.InActive = this.listCustomerNameMaster.InActive;
            masterRow.DependentOnly = this.listCustomerNameMaster.DependentOnly;
            masterRow.IsMasterCustomer = this.listCustomerNameMaster.IsMasterCustomer;
            masterRow.IsTenderCustomer = this.listCustomerNameMaster.IsTenderCustomer;

            masterRow.UserID = this.UserOrganization.UserID;
            masterRow.UserOrganizationID = this.UserOrganization.UserOrganizationID;

            if (this.listCustomerNameMaster.CustomerID <= 0) masterDataTable.AddListCustomerNameRow(masterRow);

            int rowsAffected = this.MasterTableAdapter.Update(masterRow);

            customerID = masterRow.CustomerID;

            return rowsAffected == 1;

        }


        private bool SaveDetail(int customerID)
        {
            int serialID = 0; int rowsAffected = 0;


            #region <Save collection>

            using (TransactionScope transactionScope = new TransactionScope())
            {
                serialID = 0;

                ListCustomerNameDTS.ListCustomerNameDetailDataTable detailDataTable = new ListCustomerNameDTS.ListCustomerNameDetailDataTable();

                foreach (ListCustomerNameDetail listCustomerNameDetail in this.ListCustomerNameDetailList)
                {
                    ListCustomerNameDTS.ListCustomerNameDetailRow detailRow = detailDataTable.NewListCustomerNameDetailRow();

                    detailRow.CustomerID = customerID;
                    detailRow.SerialID = ++serialID;

                    detailRow.CustomerModelID = listCustomerNameDetail.CustomerModelID;

                    detailRow.Reference = listCustomerNameDetail.Reference;
                    detailRow.Description = listCustomerNameDetail.Description;
                    detailRow.Other = listCustomerNameDetail.Other;

                    detailRow.InActive = listCustomerNameDetail.InActive;

                    detailDataTable.AddListCustomerNameDetailRow(detailRow);
                }

                rowsAffected = this.DetailTableAdapter.Update(detailDataTable);
                if (rowsAffected != this.listCustomerNameDetailList.Count) throw new System.ArgumentException("Insufficient save", "Save detail");

                ListCustomerNameDTS.ListCustomerNameSalesmenDataTable salesmenDetailDataTable = new ListCustomerNameDTS.ListCustomerNameSalesmenDataTable();

                foreach (ListCustomerNameSalesmen listCustomerNameSalesmen in this.ListCustomerNameSalesmenList)
                {
                    ListCustomerNameDTS.ListCustomerNameSalesmenRow detailRow = salesmenDetailDataTable.NewListCustomerNameSalesmenRow();

                    detailRow.CustomerID = customerID;
                    detailRow.SerialID = this.GetNewSerialID(listCustomerNameSalesmen.SalesmenID);

                    detailRow.SalesmenID = listCustomerNameSalesmen.SalesmenID;

                    detailRow.Description = listCustomerNameSalesmen.Description;
                    detailRow.Other = listCustomerNameSalesmen.Other;

                    detailRow.InActive = listCustomerNameSalesmen.InActive;

                    salesmenDetailDataTable.AddListCustomerNameSalesmenRow(detailRow);
                }

                rowsAffected = this.SalesmenDetailTableAdapter.Update(salesmenDetailDataTable);
                if (rowsAffected != this.listCustomerNameSalesmenList.Count) throw new System.ArgumentException("Insufficient save", "Save detail");

                transactionScope.Complete();
            }

            #endregion <Save collection>


            return true;
        }


        private bool SaveUndo(int customerID)
        {
            this.DetailTableAdapter.Delete(customerID);
            this.SalesmenDetailTableAdapter.Delete(customerID);

            return true;
        }



        private string GetDescriptionUPI(int addressAreaID)
        {
            string description = ""; string addressAreaCode = "";
            //On Error GoTo ERR_HANDLER
            DataTable lRec = SQLDatabase.GetDataTable("SELECT TOP 1 * FROM VWListAddressArea WHERE AddressAreaID = " + addressAreaID);
            if (lRec.Rows.Count > 0) addressAreaCode = (string)lRec.Rows[0]["AreaCode1"];

            //AUTOCODE.BEGIN
            description = pfNewIDReference6("ListCustomerName INNER JOIN VWListAddressArea ON ListCustomerName.AddressAreaID = VWListAddressArea.AddressAreaID ", "ListCustomerName.Description", 3, "WHERE VWListAddressArea.AddressAreaID1 = " + lRec.Rows[0]["AddressAreaID1"]);
            addressAreaCode = addressAreaCode.Substring(0, 2);
            if (addressAreaCode.Length == 0)
                addressAreaCode = "XX";
            else if (addressAreaCode.Length == 1)
                addressAreaCode = addressAreaCode + "X";
            if (description != "") description = addressAreaCode + description.Substring(description.Length - 6, 6);
            //AUTOCODE.END

            // CHECK AGAIN (KIEM TRUNG).BEGIN
            if (description.Length >= 6)
            {
                int referenceID = 0;
                if (int.TryParse(description.Substring(description.Length - 6, 6), out referenceID))
                {
                    do
                    {
                        DataTable lChk = SQLDatabase.GetDataTable("SELECT TOP 1 CustomerID FROM ListCustomerName WHERE Description = '" + description + "'");
                        if (lChk.Rows.Count > 0) { referenceID = referenceID + 1; description = addressAreaCode + referenceID.ToString("000000"); }
                        else break;
                    } while (true);
                }
            }
            // CHECK AGAIN (KIEM TRUNG).END

            return description;
        }

        private string pfNewIDReference6(string tableName, string fieldName, int startIndex, string conditionString)
        {
            int lReferenceID = 0; string lReferencePrefix = "U";
            //DataTable lRec = SQLDatabase.GetDataTable("SELECT MAX(CAST( SUBSTRING(" + fieldName + ", " + startIndex + ", CASE PATINDEX('%-%', " + fieldName + ") WHEN 0 THEN LEN(" + fieldName + ") - 1 ELSE PATINDEX('%-%', " + fieldName + ") - " + startIndex + " END) AS FLOAT ) ) AS ReferenceIDMax FROM " + tableName + conditionString);
            DataTable lRec = SQLDatabase.GetDataTable("SELECT MAX(CAST( SUBSTRING(" + fieldName + ", " + startIndex + ", 6) AS FLOAT ) ) AS ReferenceIDMax FROM " + tableName + conditionString);

            if (lRec.Rows.Count > 0 && lRec.Rows[0]["ReferenceIDMax"] != DBNull.Value)
                lReferenceID = (int)((double)lRec.Rows[0]["ReferenceIDMax"] + 1);
            else
                lReferenceID = 1;

            return lReferencePrefix + lReferenceID.ToString("000000");
        }


        #endregion



        private void RestoreProcedure()
        {
            string queryString;
            string[] queryArray;


            #region Listing

            queryString = "     " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";

            queryString = queryString + "       SELECT      CAST(0 AS bit) AS IsCustomer, 0 CustomerID, '' AS Description, 0 AS AncestorID, '' AS CustomerCode, '' AS ZuelligCode, '' AS DietthelmCode, '' AS GANNONCode, '' AS AreaName, '' AS Telephone, '' AS ContactName " + "\r\n";
            queryString = queryString + "       FROM        PublicMasterProfile " + "\r\n";
            queryString = queryString + "       WHERE       ProfileID < -88888 " + "\r\n"; //RETURN NONE OF ROW

            SQLDatabase.CreateStoredProcedure("ListCustomerNameEmptyListing", queryString);



            queryString = "     @AddressAreaID Int, @SalesmenID Int, @CustomerTypeID Int, @DescriptionOfficial nvarchar(100) " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";



            queryString = queryString + "       DECLARE     @ListAddressAreaFilter TABLE (AddressAreaID  int, IsAncestor bit) " + "\r\n";

            queryString = queryString + "       INSERT INTO @ListAddressAreaFilter (AddressAreaID, IsAncestor) " + "\r\n";

            queryString = queryString + "       SELECT      AddressAreaID,  0 AS IsAncestor FROM ListAddressAreaFull WHERE AddressAreaID1 = @AddressAreaID OR AddressAreaID2 = @AddressAreaID OR AddressAreaID3 = @AddressAreaID OR AddressAreaID4 = @AddressAreaID OR AddressAreaID5 = @AddressAreaID OR AddressAreaID6 = @AddressAreaID " + "\r\n";
            queryString = queryString + "       UNION ALL   " + "\r\n";
            queryString = queryString + "       SELECT      AddressAreaID1, 1 AS IsAncestor FROM ListAddressAreaFull WHERE AddressAreaID  = @AddressAreaID " + "\r\n";
            queryString = queryString + "       UNION ALL   " + "\r\n";
            queryString = queryString + "       SELECT      AddressAreaID2, 1 AS IsAncestor FROM ListAddressAreaFull WHERE AddressAreaID  = @AddressAreaID " + "\r\n";
            queryString = queryString + "       UNION ALL   " + "\r\n";
            queryString = queryString + "       SELECT      AddressAreaID3, 1 AS IsAncestor FROM ListAddressAreaFull WHERE AddressAreaID  = @AddressAreaID " + "\r\n";
            queryString = queryString + "       UNION ALL   " + "\r\n";
            queryString = queryString + "       SELECT      AddressAreaID4, 1 AS IsAncestor FROM ListAddressAreaFull WHERE AddressAreaID  = @AddressAreaID " + "\r\n";
            queryString = queryString + "       UNION ALL   " + "\r\n";
            queryString = queryString + "       SELECT      AddressAreaID5, 1 AS IsAncestor FROM ListAddressAreaFull WHERE AddressAreaID  = @AddressAreaID " + "\r\n";
            queryString = queryString + "       UNION ALL   " + "\r\n";
            queryString = queryString + "       SELECT      AddressAreaID6, 1 AS IsAncestor FROM ListAddressAreaFull WHERE AddressAreaID  = @AddressAreaID " + "\r\n";



            queryString = queryString + "       SELECT      CAST(0 AS bit) AS IsCustomer, 100000 * AddressAreaID AS CustomerID, Description, 100000 * AncestorID AS AncestorID, '' AS CustomerCode, '' AS ZuelligCode, '' AS DietthelmCode, '' AS GANNONCode, '' AS AreaName, '' AS Telephone, '' AS ContactName  " + "\r\n";
            queryString = queryString + "       FROM        ListAddressArea " + "\r\n";
            queryString = queryString + "       WHERE       AddressAreaID IN (SELECT AddressAreaID FROM @ListAddressAreaFilter) " + "\r\n";

            queryString = queryString + "       UNION ALL " + "\r\n";

            queryString = queryString + "       SELECT      CAST(1 AS bit) AS IsCustomer, ListCustomerName.CustomerID AS CustomerID, ListCustomerName.DescriptionOfficial AS Description, 100000 * ListCustomerName.AddressAreaID AS AncestorID, ListCustomerName.Description AS CustomerCode, ListCustomerNameColumnFull.DescriptionZuellig AS ZuelligCode, ListCustomerNameColumnFull.DescriptionDiethelm AS DietthelmCode, ListCustomerNameColumnFull.Description89 AS GANNONCode, ListAddressArea.Description AS AreaName, ListCustomername.Telephone, ListCustomername.ContactName " + "\r\n";
            queryString = queryString + "       FROM        ListCustomerName INNER JOIN " + "\r\n";
            queryString = queryString + "                   ListAddressArea ON ListCustomerName.AddressAreaID = ListAddressArea.AddressAreaID LEFT JOIN " + "\r\n";
            queryString = queryString + "                   ListCustomerNameColumnFull ON ListCustomerName.CustomerID = ListCustomerNameColumnFull.CustomerID " + "\r\n";
            queryString = queryString + "       WHERE       ListCustomerName.DependentOnly = 0 AND ListCustomerName.AddressAreaID IN (SELECT AddressAreaID FROM @ListAddressAreaFilter WHERE IsAncestor = 0) AND (@SalesmenID = -1 OR ListCustomerName.CustomerID IN (SELECT CustomerID FROM ListSalesmenCustomer WHERE SalesmenID = @SalesmenID)) AND (@CustomerTypeID <= 0 OR ListCustomerName.CustomerTypeID = @CustomerTypeID) AND (@DescriptionOfficial = N'' OR ListCustomerName.DescriptionOfficial LIKE N'%' + @DescriptionOfficial + '%') " + "\r\n";

            SQLDatabase.CreateStoredProcedure("ListCustomerNameAddressAreaListing", queryString);




            queryString = "     @CustomerTypeID Int, @SalesmenID Int " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";


            queryString = queryString + "       DECLARE     @ListCustomerTypeFilter TABLE (CustomerTypeID  int, IsAncestor bit) " + "\r\n";

            queryString = queryString + "       INSERT INTO @ListCustomerTypeFilter (CustomerTypeID, IsAncestor) " + "\r\n";

            queryString = queryString + "       SELECT      CustomerTypeID,  0 AS IsAncestor FROM ListCustomerTypeFull WHERE CustomerTypeID1 = @CustomerTypeID OR CustomerTypeID2 = @CustomerTypeID OR CustomerTypeID3 = @CustomerTypeID OR CustomerTypeID4 = @CustomerTypeID OR CustomerTypeID5 = @CustomerTypeID OR CustomerTypeID6 = @CustomerTypeID " + "\r\n";
            queryString = queryString + "       UNION ALL   " + "\r\n";
            queryString = queryString + "       SELECT      CustomerTypeID1, 1 AS IsAncestor FROM ListCustomerTypeFull WHERE CustomerTypeID  = @CustomerTypeID " + "\r\n";
            queryString = queryString + "       UNION ALL   " + "\r\n";
            queryString = queryString + "       SELECT      CustomerTypeID2, 1 AS IsAncestor FROM ListCustomerTypeFull WHERE CustomerTypeID  = @CustomerTypeID " + "\r\n";
            queryString = queryString + "       UNION ALL   " + "\r\n";
            queryString = queryString + "       SELECT      CustomerTypeID3, 1 AS IsAncestor FROM ListCustomerTypeFull WHERE CustomerTypeID  = @CustomerTypeID " + "\r\n";
            queryString = queryString + "       UNION ALL   " + "\r\n";
            queryString = queryString + "       SELECT      CustomerTypeID4, 1 AS IsAncestor FROM ListCustomerTypeFull WHERE CustomerTypeID  = @CustomerTypeID " + "\r\n";
            queryString = queryString + "       UNION ALL   " + "\r\n";
            queryString = queryString + "       SELECT      CustomerTypeID5, 1 AS IsAncestor FROM ListCustomerTypeFull WHERE CustomerTypeID  = @CustomerTypeID " + "\r\n";
            queryString = queryString + "       UNION ALL   " + "\r\n";
            queryString = queryString + "       SELECT      CustomerTypeID6, 1 AS IsAncestor FROM ListCustomerTypeFull WHERE CustomerTypeID  = @CustomerTypeID " + "\r\n";



            queryString = queryString + "       SELECT      CAST(0 AS bit) AS IsCustomer, 100000 * CustomerTypeID AS CustomerID, Description, 100000 * AncestorID AS AncestorID, '' AS CustomerCode, '' AS ZuelligCode, '' AS DietthelmCode, '' AS GANNONCode, '' AS AreaName, '' AS Telephone, '' AS ContactName " + "\r\n";
            queryString = queryString + "       FROM        ListCustomerType " + "\r\n";
            queryString = queryString + "       WHERE       CustomerTypeID IN (SELECT CustomerTypeID FROM @ListCustomerTypeFilter) " + "\r\n";

            queryString = queryString + "       UNION ALL " + "\r\n";

            queryString = queryString + "       SELECT      CAST(1 AS bit) AS IsCustomer, ListCustomerName.CustomerID AS CustomerID, ListCustomerName.DescriptionOfficial AS Description, 100000 * ListCustomerName.CustomerTypeID AS AncestorID, ListCustomerName.Description AS CustomerCode, ListCustomerNameColumnFull.DescriptionZuellig AS ZuelligCode, ListCustomerNameColumnFull.DescriptionDiethelm AS DietthelmCode, ListCustomerNameColumnFull.Description89 AS GANNONCode, ListAddressArea.Description AS AreaName, ListCustomername.Telephone, ListCustomername.ContactName " + "\r\n";
            queryString = queryString + "       FROM        ListCustomerName INNER JOIN " + "\r\n";
            queryString = queryString + "                   ListCustomerNameColumnFull ON ListCustomerName.CustomerID = ListCustomerNameColumnFull.CustomerID INNER JOIN  " + "\r\n";
            queryString = queryString + "                   ListAddressArea ON ListCustomerName.AddressAreaID = ListAddressArea.AddressAreaID " + "\r\n";
            queryString = queryString + "       WHERE       ListCustomerName.DependentOnly = 0 AND ListCustomerName.CustomerTypeID IN (SELECT CustomerTypeID FROM @ListCustomerTypeFilter WHERE IsAncestor = 0) AND (@SalesmenID = -1 OR ListCustomerName.CustomerID IN (SELECT CustomerID FROM ListSalesmenCustomer WHERE SalesmenID = @SalesmenID)) " + "\r\n";

            SQLDatabase.CreateStoredProcedure("ListCustomerNameCustomerTypeListing", queryString);




            queryString = "     @CustomerCategoryID Int, @SalesmenID Int " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";



            queryString = queryString + "       DECLARE     @ListCustomerCategoryFilter TABLE (CustomerCategoryID  int, IsAncestor bit) " + "\r\n";

            queryString = queryString + "       INSERT INTO @ListCustomerCategoryFilter (CustomerCategoryID, IsAncestor) " + "\r\n";

            queryString = queryString + "       SELECT      CustomerCategoryID,  0 AS IsAncestor FROM ListCustomerCategoryFull WHERE CustomerCategoryID1 = @CustomerCategoryID OR CustomerCategoryID2 = @CustomerCategoryID OR CustomerCategoryID3 = @CustomerCategoryID OR CustomerCategoryID4 = @CustomerCategoryID OR CustomerCategoryID5 = @CustomerCategoryID OR CustomerCategoryID6 = @CustomerCategoryID " + "\r\n";
            queryString = queryString + "       UNION ALL   " + "\r\n";
            queryString = queryString + "       SELECT      CustomerCategoryID1, 1 AS IsAncestor FROM ListCustomerCategoryFull WHERE CustomerCategoryID  = @CustomerCategoryID " + "\r\n";
            queryString = queryString + "       UNION ALL   " + "\r\n";
            queryString = queryString + "       SELECT      CustomerCategoryID2, 1 AS IsAncestor FROM ListCustomerCategoryFull WHERE CustomerCategoryID  = @CustomerCategoryID " + "\r\n";
            queryString = queryString + "       UNION ALL   " + "\r\n";
            queryString = queryString + "       SELECT      CustomerCategoryID3, 1 AS IsAncestor FROM ListCustomerCategoryFull WHERE CustomerCategoryID  = @CustomerCategoryID " + "\r\n";
            queryString = queryString + "       UNION ALL   " + "\r\n";
            queryString = queryString + "       SELECT      CustomerCategoryID4, 1 AS IsAncestor FROM ListCustomerCategoryFull WHERE CustomerCategoryID  = @CustomerCategoryID " + "\r\n";
            queryString = queryString + "       UNION ALL   " + "\r\n";
            queryString = queryString + "       SELECT      CustomerCategoryID5, 1 AS IsAncestor FROM ListCustomerCategoryFull WHERE CustomerCategoryID  = @CustomerCategoryID " + "\r\n";
            queryString = queryString + "       UNION ALL   " + "\r\n";
            queryString = queryString + "       SELECT      CustomerCategoryID6, 1 AS IsAncestor FROM ListCustomerCategoryFull WHERE CustomerCategoryID  = @CustomerCategoryID " + "\r\n";



            queryString = queryString + "       SELECT      CAST(0 AS bit) AS IsCustomer, 100000 * CustomerCategoryID AS CustomerID, Description, 100000 * AncestorID AS AncestorID, '' AS CustomerCode, '' AS ZuelligCode, '' AS DietthelmCode, '' AS GANNONCode, '' AS AreaName, '' AS Telephone, '' AS ContactName " + "\r\n";
            queryString = queryString + "       FROM        ListCustomerCategory " + "\r\n";
            queryString = queryString + "       WHERE       CustomerCategoryID IN (SELECT CustomerCategoryID FROM @ListCustomerCategoryFilter) " + "\r\n";

            queryString = queryString + "       UNION ALL " + "\r\n";

            queryString = queryString + "       SELECT      CAST(1 AS bit) AS IsCustomer, ListCustomerName.CustomerID AS CustomerID, ListCustomerName.DescriptionOfficial AS Description, 100000 * ListCustomerName.CustomerCategoryID AS AncestorID, ListCustomerName.Description AS CustomerCode, ListCustomerNameColumnFull.DescriptionZuellig AS ZuelligCode, ListCustomerNameColumnFull.DescriptionDiethelm AS DietthelmCode, ListCustomerNameColumnFull.Description89 AS GANNONCode, ListAddressArea.Description AS AreaName, ListCustomername.Telephone, ListCustomername.ContactName " + "\r\n";
            queryString = queryString + "       FROM        ListCustomerName INNER JOIN " + "\r\n";
            queryString = queryString + "                   ListCustomerNameColumnFull ON ListCustomerName.CustomerID = ListCustomerNameColumnFull.CustomerID INNER JOIN  " + "\r\n";
            queryString = queryString + "                   ListAddressArea ON ListCustomerName.AddressAreaID = ListAddressArea.AddressAreaID " + "\r\n";
            queryString = queryString + "       WHERE       ListCustomerName.DependentOnly = 0 AND ListCustomerName.CustomerCategoryID IN (SELECT CustomerCategoryID FROM @ListCustomerCategoryFilter WHERE IsAncestor = 0) AND (@SalesmenID = -1 OR ListCustomerName.CustomerID IN (SELECT CustomerID FROM ListSalesmenCustomer WHERE SalesmenID = @SalesmenID)) " + "\r\n";

            SQLDatabase.CreateStoredProcedure("ListCustomerNameCustomerCategoryListing", queryString);




            queryString = "     @CustomerChannelID Int, @SalesmenID Int " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";



            queryString = queryString + "       DECLARE     @ListCustomerChannelFilter TABLE (CustomerChannelID  int, IsAncestor bit) " + "\r\n";

            queryString = queryString + "       INSERT INTO @ListCustomerChannelFilter (CustomerChannelID, IsAncestor) " + "\r\n";

            queryString = queryString + "       SELECT      CustomerChannelID,  0 AS IsAncestor FROM ListCustomerChannelFull WHERE CustomerChannelID1 = @CustomerChannelID OR CustomerChannelID2 = @CustomerChannelID OR CustomerChannelID3 = @CustomerChannelID OR CustomerChannelID4 = @CustomerChannelID OR CustomerChannelID5 = @CustomerChannelID OR CustomerChannelID6 = @CustomerChannelID " + "\r\n";
            queryString = queryString + "       UNION ALL   " + "\r\n";
            queryString = queryString + "       SELECT      CustomerChannelID1, 1 AS IsAncestor FROM ListCustomerChannelFull WHERE CustomerChannelID  = @CustomerChannelID " + "\r\n";
            queryString = queryString + "       UNION ALL   " + "\r\n";
            queryString = queryString + "       SELECT      CustomerChannelID2, 1 AS IsAncestor FROM ListCustomerChannelFull WHERE CustomerChannelID  = @CustomerChannelID " + "\r\n";
            queryString = queryString + "       UNION ALL   " + "\r\n";
            queryString = queryString + "       SELECT      CustomerChannelID3, 1 AS IsAncestor FROM ListCustomerChannelFull WHERE CustomerChannelID  = @CustomerChannelID " + "\r\n";
            queryString = queryString + "       UNION ALL   " + "\r\n";
            queryString = queryString + "       SELECT      CustomerChannelID4, 1 AS IsAncestor FROM ListCustomerChannelFull WHERE CustomerChannelID  = @CustomerChannelID " + "\r\n";
            queryString = queryString + "       UNION ALL   " + "\r\n";
            queryString = queryString + "       SELECT      CustomerChannelID5, 1 AS IsAncestor FROM ListCustomerChannelFull WHERE CustomerChannelID  = @CustomerChannelID " + "\r\n";
            queryString = queryString + "       UNION ALL   " + "\r\n";
            queryString = queryString + "       SELECT      CustomerChannelID6, 1 AS IsAncestor FROM ListCustomerChannelFull WHERE CustomerChannelID  = @CustomerChannelID " + "\r\n";



            queryString = queryString + "       SELECT      CAST(0 AS bit) AS IsCustomer, 100000 * CustomerChannelID AS CustomerID, Description, 100000 * AncestorID AS AncestorID, '' AS CustomerCode, '' AS ZuelligCode, '' AS DietthelmCode, '' AS GANNONCode, '' AS AreaName, '' AS Telephone, '' AS ContactName " + "\r\n";
            queryString = queryString + "       FROM        ListCustomerChannel " + "\r\n";
            queryString = queryString + "       WHERE       CustomerChannelID IN (SELECT CustomerChannelID FROM @ListCustomerChannelFilter) " + "\r\n";

            queryString = queryString + "       UNION ALL " + "\r\n";

            queryString = queryString + "       SELECT      CAST(1 AS bit) AS IsCustomer, ListCustomerName.CustomerID AS CustomerID, ListCustomerName.DescriptionOfficial AS Description, 100000 * ListCustomerName.CustomerChannelID AS AncestorID, ListCustomerName.Description AS CustomerCode, ListCustomerNameColumnFull.DescriptionZuellig AS ZuelligCode, ListCustomerNameColumnFull.DescriptionDiethelm AS DietthelmCode, ListCustomerNameColumnFull.Description89 AS GANNONCode, ListAddressArea.Description AS AreaName, ListCustomername.Telephone, ListCustomername.ContactName " + "\r\n";
            queryString = queryString + "       FROM        ListCustomerName INNER JOIN " + "\r\n";
            queryString = queryString + "                   ListCustomerNameColumnFull ON ListCustomerName.CustomerID = ListCustomerNameColumnFull.CustomerID INNER JOIN  " + "\r\n";
            queryString = queryString + "                   ListAddressArea ON ListCustomerName.AddressAreaID = ListAddressArea.AddressAreaID  " + "\r\n";
            queryString = queryString + "       WHERE       ListCustomerName.DependentOnly = 0 AND ListCustomerName.CustomerChannelID IN (SELECT CustomerChannelID FROM @ListCustomerChannelFilter WHERE IsAncestor = 0) AND (@SalesmenID = -1 OR ListCustomerName.CustomerID IN (SELECT CustomerID FROM ListSalesmenCustomer WHERE SalesmenID = @SalesmenID)) " + "\r\n";

            SQLDatabase.CreateStoredProcedure("ListCustomerNameCustomerChannelListing", queryString);




            queryString = "     @SalesmenID Int " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";


            queryString = queryString + "       DECLARE     @ListMasterCustomerAddressAreaFilter TABLE (AddressAreaID  int) " + "\r\n";
            queryString = queryString + "       INSERT INTO @ListMasterCustomerAddressAreaFilter (AddressAreaID) " + "\r\n";
            queryString = queryString + "       SELECT      DISTINCT AddressAreaID FROM ListCustomerName WHERE IsMasterCustomer = 1 " + "\r\n";

            queryString = queryString + "       DECLARE     @ListAddressAreaFilter TABLE (AddressAreaID  int, IsAncestor bit) " + "\r\n";

            queryString = queryString + "       INSERT INTO @ListAddressAreaFilter (AddressAreaID, IsAncestor) " + "\r\n";

            queryString = queryString + "       SELECT      AddressAreaID1, 1 AS IsAncestor FROM ListAddressAreaFull WHERE AddressAreaID IN (SELECT AddressAreaID FROM @ListMasterCustomerAddressAreaFilter) " + "\r\n";
            queryString = queryString + "       UNION ALL   " + "\r\n";
            queryString = queryString + "       SELECT      AddressAreaID2, 1 AS IsAncestor FROM ListAddressAreaFull WHERE AddressAreaID IN (SELECT AddressAreaID FROM @ListMasterCustomerAddressAreaFilter)  " + "\r\n";
            queryString = queryString + "       UNION ALL   " + "\r\n";
            queryString = queryString + "       SELECT      AddressAreaID3, 1 AS IsAncestor FROM ListAddressAreaFull WHERE AddressAreaID IN (SELECT AddressAreaID FROM @ListMasterCustomerAddressAreaFilter)  " + "\r\n";
            queryString = queryString + "       UNION ALL   " + "\r\n";
            queryString = queryString + "       SELECT      AddressAreaID4, 1 AS IsAncestor FROM ListAddressAreaFull WHERE AddressAreaID IN (SELECT AddressAreaID FROM @ListMasterCustomerAddressAreaFilter)  " + "\r\n";
            queryString = queryString + "       UNION ALL   " + "\r\n";
            queryString = queryString + "       SELECT      AddressAreaID5, 1 AS IsAncestor FROM ListAddressAreaFull WHERE AddressAreaID IN (SELECT AddressAreaID FROM @ListMasterCustomerAddressAreaFilter)  " + "\r\n";
            queryString = queryString + "       UNION ALL   " + "\r\n";
            queryString = queryString + "       SELECT      AddressAreaID6, 1 AS IsAncestor FROM ListAddressAreaFull WHERE AddressAreaID IN (SELECT AddressAreaID FROM @ListMasterCustomerAddressAreaFilter)  " + "\r\n";



            queryString = queryString + "       SELECT      CAST(0 AS bit) AS IsCustomer, 100000 * AddressAreaID AS CustomerID, Description, 100000 * AncestorID AS AncestorID, '' AS CustomerCode, '' AS ZuelligCode, '' AS DietthelmCode, '' AS GANNONCode, '' AS AreaName, '' AS Telephone, '' AS ContactName " + "\r\n";
            queryString = queryString + "       FROM        ListAddressArea " + "\r\n";
            queryString = queryString + "       WHERE       AddressAreaID IN (SELECT AddressAreaID FROM @ListAddressAreaFilter) " + "\r\n";

            queryString = queryString + "       UNION ALL " + "\r\n";

            queryString = queryString + "       SELECT      CAST(1 AS bit) AS IsCustomer, ListCustomerName.CustomerID AS CustomerID, ListCustomerName.DescriptionOfficial AS Description, 100000 * ListCustomerName.AddressAreaID AS AncestorID, ListCustomerName.Description AS CustomerCode, ListCustomerNameColumnFull.DescriptionZuellig AS ZuelligCode, ListCustomerNameColumnFull.DescriptionDiethelm AS DietthelmCode, ListCustomerNameColumnFull.Description89 AS GANNONCode, ListAddressArea.Description AS AreaName, ListCustomername.Telephone, ListCustomername.ContactName " + "\r\n";
            queryString = queryString + "       FROM        ListCustomerName INNER JOIN " + "\r\n";
            queryString = queryString + "                   ListCustomerNameColumnFull ON ListCustomerName.CustomerID = ListCustomerNameColumnFull.CustomerID INNER JOIN  " + "\r\n";
            queryString = queryString + "                   ListAddressArea ON ListCustomerName.AddressAreaID = ListAddressArea.AddressAreaID  " + "\r\n";
            queryString = queryString + "       WHERE       ListCustomerName.DependentOnly = 0 AND ListCustomerName.IsMasterCustomer = 1 AND (@SalesmenID = -1 OR ListCustomerName.CustomerID IN (SELECT CustomerID FROM ListSalesmenCustomer WHERE SalesmenID = @SalesmenID)) " + "\r\n";

            SQLDatabase.CreateStoredProcedure("ListCustomerNameMasterCustomerListing", queryString);




            queryString = "     @SalesmenID Int " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";


            queryString = queryString + "       DECLARE     @ListTenderCustomerChannelFilter TABLE (CustomerChannelID  int) " + "\r\n";
            queryString = queryString + "       INSERT INTO @ListTenderCustomerChannelFilter (CustomerChannelID) " + "\r\n";
            queryString = queryString + "       SELECT      DISTINCT CustomerChannelID FROM ListCustomerName WHERE IsTenderCustomer = 1 " + "\r\n";

            queryString = queryString + "       DECLARE     @ListCustomerChannelFilter TABLE (CustomerChannelID  int, IsAncestor bit) " + "\r\n";

            queryString = queryString + "       INSERT INTO @ListCustomerChannelFilter (CustomerChannelID, IsAncestor) " + "\r\n";

            queryString = queryString + "       SELECT      CustomerChannelID1, 1 AS IsAncestor FROM ListCustomerChannelFull WHERE CustomerChannelID IN (SELECT CustomerChannelID FROM @ListTenderCustomerChannelFilter) " + "\r\n";
            queryString = queryString + "       UNION ALL   " + "\r\n";
            queryString = queryString + "       SELECT      CustomerChannelID2, 1 AS IsAncestor FROM ListCustomerChannelFull WHERE CustomerChannelID IN (SELECT CustomerChannelID FROM @ListTenderCustomerChannelFilter)  " + "\r\n";
            queryString = queryString + "       UNION ALL   " + "\r\n";
            queryString = queryString + "       SELECT      CustomerChannelID3, 1 AS IsAncestor FROM ListCustomerChannelFull WHERE CustomerChannelID IN (SELECT CustomerChannelID FROM @ListTenderCustomerChannelFilter)  " + "\r\n";
            queryString = queryString + "       UNION ALL   " + "\r\n";
            queryString = queryString + "       SELECT      CustomerChannelID4, 1 AS IsAncestor FROM ListCustomerChannelFull WHERE CustomerChannelID IN (SELECT CustomerChannelID FROM @ListTenderCustomerChannelFilter)  " + "\r\n";
            queryString = queryString + "       UNION ALL   " + "\r\n";
            queryString = queryString + "       SELECT      CustomerChannelID5, 1 AS IsAncestor FROM ListCustomerChannelFull WHERE CustomerChannelID IN (SELECT CustomerChannelID FROM @ListTenderCustomerChannelFilter)  " + "\r\n";
            queryString = queryString + "       UNION ALL   " + "\r\n";
            queryString = queryString + "       SELECT      CustomerChannelID6, 1 AS IsAncestor FROM ListCustomerChannelFull WHERE CustomerChannelID IN (SELECT CustomerChannelID FROM @ListTenderCustomerChannelFilter)  " + "\r\n";



            queryString = queryString + "       SELECT      CAST(0 AS bit) AS IsCustomer, 100000 * CustomerChannelID AS CustomerID, Description, 100000 * AncestorID AS AncestorID, '' AS CustomerCode, '' AS ZuelligCode, '' AS DietthelmCode, '' AS GANNONCode, '' AS AreaName, '' AS Telephone, '' AS ContactName " + "\r\n";
            queryString = queryString + "       FROM        ListCustomerChannel " + "\r\n";
            queryString = queryString + "       WHERE       CustomerChannelID IN (SELECT CustomerChannelID FROM @ListCustomerChannelFilter) " + "\r\n";

            queryString = queryString + "       UNION ALL " + "\r\n";


            queryString = queryString + "       SELECT      CAST(1 AS bit) AS IsCustomer, ListCustomerName.CustomerID AS CustomerID, ListCustomerName.DescriptionOfficial AS Description, 100000 * ListCustomerName.CustomerChannelID AS AncestorID, ListCustomerName.Description AS CustomerCode, ListCustomerNameColumnFull.DescriptionZuellig AS ZuelligCode, ListCustomerNameColumnFull.DescriptionDiethelm AS DietthelmCode, ListCustomerNameColumnFull.Description89 AS GANNONCode, ListAddressArea.Description AS AreaName, ListCustomername.Telephone, ListCustomername.ContactName " + "\r\n";
            queryString = queryString + "       FROM        ListCustomerName INNER JOIN " + "\r\n";
            queryString = queryString + "                   ListAddressArea ON ListCustomerName.AddressAreaID = ListAddressArea.AddressAreaID LEFT JOIN  " + "\r\n";
            queryString = queryString + "                   ListCustomerNameColumnFull ON ListCustomerName.CustomerID = ListCustomerNameColumnFull.CustomerID " + "\r\n";

            queryString = queryString + "       WHERE       ListCustomerName.DependentOnly = 0 AND ListCustomerName.IsTenderCustomer = 1 AND (@SalesmenID = -1 OR ListCustomerName.CustomerID IN (SELECT CustomerID FROM ListSalesmenCustomer WHERE SalesmenID = @SalesmenID)) " + "\r\n";

            SQLDatabase.CreateStoredProcedure("ListCustomerNameTenderCustomerListing", queryString);

            #endregion Listing



            queryString = "     @CustomerID Int " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       SELECT      CustomerID, CustomerCategoryID, CustomerTypeID, SalesmenID, Description, DescriptionOfficial, DescriptionOriginal, VATCode, ContactName, AttentionName, Telephone, EMailAddress, AddressNo, AddressStreet, AddressAreaID, Other, DependentOnly, DistributorID, CustomerGroupID, IsMasterCustomer, MasterCustomerID, CustomerChannelID, IsTenderCustomer, InActive, EntryDate, UserID, UserOrganizationID " + "\r\n";
            queryString = queryString + "       FROM        ListCustomerName " + "\r\n";
            queryString = queryString + "       WHERE       CustomerID = @CustomerID " + "\r\n";

            SQLDatabase.CreateStoredProcedure("ListCustomerNameSelect", queryString);


            queryString = "     @CustomerCategoryID int, @CustomerTypeID int, @SalesmenID int, @Description nvarchar(100), @DescriptionOfficial nvarchar(100), @DescriptionOriginal nvarchar(100), @VATCode nvarchar(60), @ContactName nvarchar(100), @AttentionName nvarchar(60), @Telephone nvarchar(60), @EMailAddress nvarchar(60), @AddressNo nvarchar(150), @AddressStreet nvarchar(150), @AddressAreaID int, @Other nvarchar(100), @DependentOnly bit, @DistributorID int, @CustomerGroupID int, @IsMasterCustomer bit, @MasterCustomerID int, @CustomerChannelID int, @IsTenderCustomer bit, @InActive bit, @EntryDate datetime, @UserID int, @UserOrganizationID int " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       INSERT INTO ListCustomerName (CustomerCategoryID, CustomerTypeID, SalesmenID, Description, DescriptionOfficial, DescriptionOriginal, VATCode, ContactName, AttentionName, Telephone, EMailAddress, AddressNo, AddressStreet, AddressAreaID, Other, DependentOnly, DistributorID, CustomerGroupID, IsMasterCustomer, MasterCustomerID, CustomerChannelID, IsTenderCustomer, InActive, EntryDate, UserID, UserOrganizationID) VALUES      (@CustomerCategoryID, @CustomerTypeID, @SalesmenID, @Description, @DescriptionOfficial, @DescriptionOriginal, @VATCode, @ContactName, @AttentionName, @Telephone, @EMailAddress, @AddressNo, @AddressStreet, @AddressAreaID, @Other, @DependentOnly, @DistributorID, @CustomerGroupID, @IsMasterCustomer, @MasterCustomerID, @CustomerChannelID, @IsTenderCustomer, @InActive, @EntryDate, @UserID, @UserOrganizationID) " + "\r\n";
            queryString = queryString + "       SELECT      CustomerID, CustomerCategoryID, CustomerTypeID, SalesmenID, Description, DescriptionOfficial, DescriptionOriginal, VATCode, ContactName, AttentionName, Telephone, EMailAddress, AddressNo, AddressStreet, AddressAreaID, Other, DependentOnly, DistributorID, CustomerGroupID, IsMasterCustomer, MasterCustomerID, CustomerChannelID, IsTenderCustomer, InActive, EntryDate, UserID, UserOrganizationID FROM ListCustomerName WHERE CustomerID = SCOPE_IDENTITY() " + "\r\n";

            SQLDatabase.CreateStoredProcedure("ListCustomerNameInsert", queryString);


            queryString = "     @CustomerID int, @CustomerCategoryID int, @CustomerTypeID int, @SalesmenID int, @Description nvarchar(100), @DescriptionOfficial nvarchar(100), @DescriptionOriginal nvarchar(100), @VATCode nvarchar(60), @ContactName nvarchar(100), @AttentionName nvarchar(60), @Telephone nvarchar(60), @EMailAddress nvarchar(60), @AddressNo nvarchar(150), @AddressStreet nvarchar(150), @AddressAreaID int, @Other nvarchar(100), @DependentOnly bit, @DistributorID int, @CustomerGroupID int, @IsMasterCustomer bit, @MasterCustomerID int, @CustomerChannelID int, @IsTenderCustomer bit, @InActive bit, @EntryDate datetime, @UserID int, @UserOrganizationID int " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       UPDATE      ListCustomerName SET CustomerCategoryID = @CustomerCategoryID, CustomerTypeID = @CustomerTypeID, SalesmenID = @SalesmenID, Description = @Description, DescriptionOfficial = @DescriptionOfficial, DescriptionOriginal = @DescriptionOriginal, VATCode = @VATCode, ContactName = @ContactName, AttentionName = @AttentionName, Telephone = @Telephone, EMailAddress = @EMailAddress, AddressNo = @AddressNo, AddressStreet = @AddressStreet, AddressAreaID = @AddressAreaID, Other = @Other, DependentOnly = @DependentOnly, DistributorID = @DistributorID, CustomerGroupID = @CustomerGroupID, IsMasterCustomer = @IsMasterCustomer, MasterCustomerID = @MasterCustomerID, CustomerChannelID= @CustomerChannelID, IsTenderCustomer = @IsTenderCustomer, InActive = @InActive, EntryDate = @EntryDate, UserID = @UserID, UserOrganizationID = @UserOrganizationID WHERE CustomerID = @CustomerID " + "\r\n";
            queryString = queryString + "       SELECT      CustomerID, CustomerCategoryID, CustomerTypeID, SalesmenID, Description, DescriptionOfficial, DescriptionOriginal, VATCode, ContactName, AttentionName, Telephone, EMailAddress, AddressNo, AddressStreet, AddressAreaID, Other, DependentOnly, DistributorID, CustomerGroupID, IsMasterCustomer, MasterCustomerID, CustomerChannelID, IsTenderCustomer, InActive, EntryDate, UserID, UserOrganizationID FROM ListCustomerName WHERE CustomerID = @CustomerID " + "\r\n";

            SQLDatabase.CreateStoredProcedure("ListCustomerNameUpdate", queryString);


            queryString = "     @CustomerID int ";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       DELETE FROM ListCustomerName WHERE CustomerID = @CustomerID " + "\r\n";
            SQLDatabase.CreateStoredProcedure("ListCustomerNameDelete", queryString);






            queryString = "     @CustomerID int " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "      SELECT       CustomerID, SerialID, CustomerModelID, Reference, Description, Other, InActive FROM ListCustomerNameDetail WHERE CustomerID = @CustomerID " + "\r\n";

            SQLDatabase.CreateStoredProcedure("ListCustomerNameDetailSelect", queryString);


            queryString = "     @CustomerID int, @SerialID int,	@CustomerModelID int, @Reference nvarchar(100), @Description nvarchar(100), @Other nvarchar(100), @InActive bit " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       INSERT INTO ListCustomerNameDetail (CustomerID, SerialID, CustomerModelID, Reference, Description, Other, InActive) VALUES (@CustomerID, @SerialID, @CustomerModelID, @Reference, @Description, @Other, @InActive) " + "\r\n";
            queryString = queryString + "       SELECT      CustomerID, SerialID, CustomerModelID, Reference, Description, Other, InActive FROM ListCustomerNameDetail WHERE CustomerID = @CustomerID AND SerialID = @SerialID " + "\r\n";

            SQLDatabase.CreateStoredProcedure("ListCustomerNameDetailInsert", queryString);


            queryString = "     @CustomerID int, @SerialID int,	@CustomerModelID int, @Reference nvarchar(100), @Description nvarchar(100), @Other nvarchar(100), @InActive bit " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       UPDATE      ListCustomerNameDetail SET CustomerModelID = @CustomerModelID, Reference = @Reference, Description = @Description, Other = @Other, InActive = @InActive WHERE CustomerID = @CustomerID AND SerialID = @SerialID " + "\r\n";
            queryString = queryString + "       SELECT      CustomerID, SerialID, CustomerModelID, Reference, Description, Other, InActive FROM ListCustomerNameDetail WHERE CustomerID = @CustomerID AND SerialID = @SerialID " + "\r\n";

            SQLDatabase.CreateStoredProcedure("ListCustomerNameDetailUpdate", queryString);


            queryString = "     @CustomerID int " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       DELETE FROM ListCustomerNameDetail WHERE CustomerID = @CustomerID " + "\r\n";

            SQLDatabase.CreateStoredProcedure("ListCustomerNameDetailDelete", queryString);






            queryString = "     @CustomerID int " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "      SELECT       CustomerID, SerialID, SalesmenID, Description, Other, InActive FROM ListSalesmenCustomer WHERE CustomerID = @CustomerID ";

            SQLDatabase.CreateStoredProcedure("ListCustomerNameSalesmenSelect", queryString);


            queryString = "     @CustomerID int, @SerialID int,	@SalesmenID int, @Description nvarchar(100), @Other nvarchar(100), @InActive bit " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       INSERT INTO ListSalesmenCustomer (CustomerID, SerialID, SalesmenID, Description, Other, InActive) VALUES (@CustomerID, @SerialID, @SalesmenID, @Description, @Other, @InActive) " + "\r\n";
            queryString = queryString + "       SELECT      CustomerID, SerialID, SalesmenID, Description, Other, InActive FROM ListSalesmenCustomer WHERE CustomerID = @CustomerID AND SerialID = @SerialID " + "\r\n";

            SQLDatabase.CreateStoredProcedure("ListCustomerNameSalesmenInsert", queryString);


            queryString = "     @CustomerID int, @SerialID int,	@SalesmenID int, @Description nvarchar(100), @Other nvarchar(100), @InActive bit " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       UPDATE      ListSalesmenCustomer SET SalesmenID = @SalesmenID, Description = @Description, Other = @Other, InActive = @InActive WHERE CustomerID = @CustomerID AND SerialID = @SerialID " + "\r\n";
            queryString = queryString + "       SELECT      CustomerID, SerialID, SalesmenID, Description, Other, InActive FROM ListSalesmenCustomer WHERE CustomerID = @CustomerID AND SerialID = @SerialID " + "\r\n";

            SQLDatabase.CreateStoredProcedure("ListCustomerNameSalesmenUpdate", queryString);


            queryString = "     @CustomerID int " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       DELETE FROM ListSalesmenCustomer WHERE CustomerID = @CustomerID " + "\r\n";
            SQLDatabase.CreateStoredProcedure("ListCustomerNameSalesmenDelete", queryString);






            /// <summary>
            /// Check for editable
            /// </summary>
            queryArray = new string[3];
            queryArray[0] = "   SELECT      TOP 1 CustomerID FROM ListSalesmenCustomer WHERE CustomerID = @FindIdentityID ";
            queryArray[1] = "   SELECT      TOP 1 CustomerID FROM ListExceptionSalesmen WHERE CustomerID = @FindIdentityID ";
            queryArray[2] = "   SELECT      TOP 1 CustomerID FROM ListCustomerName WHERE MasterCustomerID = @FindIdentityID ";

            SQLDatabase.CreateProcedureToCheckExisting("ListCustomerNameEditable", queryArray);
        }

    }
}
